﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace PIM_ATE
{
    class ClsDataConfigSelectBuilder
    {
        const string TABLE_NAME = "tb_MOIDConfig";
        string m_sMOID;
        string m_sConnect;

        public static void createTable()
        {
            //CREATE TABLE [tb_MOIDConfig] (
            //[ID] INT IDENTITY(1,1) NOT NULL,
            //[MOID] nvarchar(100) NOT NULL,
            //[ProCode] NVARCHAR(100) NOT NULL,
            //[PIM_Select] varchar(200) NULL,
            //[ELEC_Select] varchar(200) NULL,
            //[Settings] XML NULL,
            //[Remark] NVARCHAR(100) NULL
            //);
        }

        public static List<string> getColNames()
        {
            List<string> names = new List<string>();
            names.Add("ID");
            names.Add("MOID");
            names.Add("ProCode");
            names.Add("PIM_Select");
            names.Add("ELEC_Select");
            names.Add("Settings");
            names.Add("Remark");
            return names;
        }

        public static string getColNamesStr(int index = 1)
        {
            string[] lstCol = getColNames().ToArray();
            string sColNames = "";
            for (int i = index; i <= lstCol.Length - 1; i++)
            {
                if (i != lstCol.Length - 1)
                    sColNames += "[" + lstCol[i] + "],";
                else
                    sColNames += "[" + lstCol[i] + "]";
            }
            return sColNames;
        }

        public void construct(string sMOID)
        {
            m_sMOID = sMOID;
        }

        public ClsDataConfigSelectBuilder(string alias)
        {
            m_sConnect = alias;
        }

        public List<bool> downloadPIMSelect()
        {
            return downloadSelect("PIM_Select");
        }

        public List<bool> downloadELECSelect()
        {
            return downloadSelect("ELEC_Select");
        }

        public string[] download()
        {
            try
            {
                List<string> lstCode = new List<string>();
                string sSql = string.Format("select distinct [MOID] from {0}", TABLE_NAME);
                DataTable dtMOID = MicroDBHelper.ExecuteDataTable(sSql, null, m_sConnect);
                for (int i = 0; i < dtMOID.Rows.Count; i++)
                {
                    lstCode.Add(dtMOID.Rows[i][0].ToString().ToUpper());
                }
                return lstCode.ToArray();
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        public bool updatePIMSelect(string sProcode, string sSelectInfo)
        {
            return updateSelect("PIM_Select", sProcode, sSelectInfo, "");
        }

        public bool updateELECSelect(string sProcode, string sSelectInfo, ClsDataSpecial cSpecial)
        {
            string sSettings = XmlUtil.ToXml(cSpecial, false, true);
            return updateSelect("ELEC_Select", sProcode, sSelectInfo, sSettings);
        }

        public ClsDataSpecial downladSpecial()
        {
            if (m_sMOID == null || m_sMOID == "")
            {//错误
                return null;
            }

            try
            {
                ClsDataSpecial ret;
                List<SqlParameter> lstParam = new List<SqlParameter>();
                lstParam.Add(new SqlParameter("@id", m_sMOID));

                string sSql = "select [Settings] from " + TABLE_NAME + " where [MOID] = @id";
                string sXml = MicroDBHelper.ExecuteScalar(sSql, lstParam.ToArray(), m_sConnect) as string;
                if (sXml == null || sXml == "")
                {//未发现特采，初始化无效特采
                    ret = new ClsDataSpecial();
                    ret.bEnable = false;
                }
                else
                {
                    ret = (ClsDataSpecial)XmlUtil.XmlTo(sXml, typeof(ClsDataSpecial));
                }

                return ret;
            }
            catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); return null; }
        }

        private List<bool> downloadSelect(string name)
        {
            if (m_sMOID == null || m_sMOID == "")
            {//错误
                return null;
            }

            try
            {
                List<bool> lstValue = new List<bool>();
                List<SqlParameter> lstParam = new List<SqlParameter>();
                lstParam.Add(new SqlParameter("@id", m_sMOID));

                string sSql = "select [" + name + "] from " + TABLE_NAME + " where [MOID] = @id";
                string sXml = MicroDBHelper.ExecuteScalar(sSql, lstParam.ToArray(), m_sConnect) as string;
                if (sXml == null) sXml = "";

                for (int i = 0; i < sXml.Length; i++)
                {
                    lstValue.Add(sXml[i] == '1');
                }
                return lstValue;
            }
            catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); return null; }
        }

        private bool updateSelect(string sType, string sProcode, string sSelectInfo, string sSettings)
        {
            if (m_sMOID == null || m_sMOID == "")
            {//错误
                return false;
            }
            int ret = -1;
            try
            {
                using (var tran = MicroDBHelper.UseTransaction(IsolationLevel.RepeatableRead, m_sConnect))
                {//开始上传
                    string sSql = string.Format("select count(*) from {0} where [MOID] = '{1}';", TABLE_NAME, m_sMOID);
                    int n = (int)MicroDBHelper.ExecuteScalar(sSql, null, tran);

                    if (n > 0)
                    {
                        List<SqlParameter> lstParam = new List<SqlParameter>();
                        lstParam.Add(new SqlParameter("@id", m_sMOID));
                        lstParam.Add(new SqlParameter("@value", sSelectInfo));
                        lstParam.Add(new SqlParameter("@settings", sSettings));

                        sSql = "update " + TABLE_NAME + " set [" + sType + "] = @value , [Settings] = @settings where [MOID] = @id;";
                        ret = MicroDBHelper.ExecuteNonQuery(sSql, lstParam.ToArray(), tran);
                        tran.MarkSuccess();
                    }
                    else
                    {
                        string sValue = "'" + m_sMOID + "','" + sProcode + "','" + sSelectInfo + "','" + sSettings + "'";
                        sSql = String.Format("insert into {0} ([MOID],[ProCode],[{1}],[Settings]) values ({2});", TABLE_NAME, sType, sValue);
                        MicroDBHelper.ExecuteNonQuery(sSql, null, m_sConnect);
                        tran.MarkSuccess();
                    }
                }
            }
            catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); }
            return ret > 0;
        }
    }

    class ClsDataGlobalParamBuilder
    {
        const string TABLE_NAME = "tb_GlobalSetup";
        string m_sConnect;

        public ClsDataGlobalParamBuilder(string alias)
        {
            m_sConnect = alias;
        }

        public static void createTable()
        {
            //CREATE TABLE [tb_GlobalSetup] (
            //[ID] INT IDENTITY(1,1) NOT NULL,
            //[PramName] NVARCHAR(100) NOT NULL,
            //[Value] NVARCHAR(200) NULL,
            //[Remark] NVARCHAR(100) NULL
            //);
        }

        public string download(string param)
        {
            try
            {
                List<string> lstCode = new List<string>();
                string sSql = string.Format("select [Value] from {0} where [PramName] = '{1}'", TABLE_NAME, param);
                string ret = (string)MicroDBHelper.ExecuteScalar(sSql, null, m_sConnect);
                return ret == null ? "" : ret;
            }
            catch
            {
                return "";
            }
        }

        public bool update(string param, string value)
        {
            int ret = -1;
            try
            {
                using (var tran = MicroDBHelper.UseTransaction(IsolationLevel.RepeatableRead, m_sConnect))
                {//开始上传
                    string sSql = string.Format("select count(*) from {0} where [PramName] = '{1}';", TABLE_NAME, param);
                    int n = (int)MicroDBHelper.ExecuteScalar(sSql, null, tran);

                    if (n > 0)
                    {
                        List<SqlParameter> lstParam = new List<SqlParameter>();
                        lstParam.Add(new SqlParameter("@id", param));
                        lstParam.Add(new SqlParameter("@value", value));

                        sSql = "update " + TABLE_NAME + " set [Value] = @value where [PramName] = @id;";
                        ret = MicroDBHelper.ExecuteNonQuery(sSql, lstParam.ToArray(), tran);
                        tran.MarkSuccess();
                    }
                    else
                    {
                        string sValue = "'" + param + "','" + value + "'";
                        sSql = String.Format("insert into {0} ([PramName],[Value]) values ({1});", TABLE_NAME, sValue);
                        MicroDBHelper.ExecuteNonQuery(sSql, null, m_sConnect);
                        tran.MarkSuccess();
                    }
                }
            }
            catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); }
            return ret > 0;
        }
    }
}
